package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bfc.BFCMessage;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.bpm.BPMTaskBase;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.sfc.SFCQualityLossBPM;
import com.iplant.aps.service.po.sfc.SFCQualityLossBPMItem;
import com.iplant.aps.service.utils.CloneTool;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.CoreServiceImpl;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.dao.TaskBaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import com.iplant.aps.serviceimpl.utils.aps.APSUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class SFCQualityLossBPMDAO extends BaseDAO implements TaskBaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCQualityLossBPMDAO.class);

    private static SFCQualityLossBPMDAO Instance = null;

    private SFCQualityLossBPMDAO() {
        super();
    }

    public static SFCQualityLossBPMDAO getInstance() {
        if (Instance == null)
            Instance = new SFCQualityLossBPMDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public SFCQualityLossBPM Update(BMSEmployee wLoginUser, SFCQualityLossBPM wSFCQualityLossBPM,
                                    OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wSFCQualityLossBPM;
            }

            if (wSFCQualityLossBPM == null)
                return null;

            String wSQL = "";
            if (wSFCQualityLossBPM.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_qualitylossbpm(Code,FlowType,FlowID,UpFlowID," +
                                "FollowerID,Status,StatusText,CreateTime,SubmitTime,OrderID,PartID,PartPointID," +
                                "Disposal,CraftsmanIDs,TechnicalEngineerIDs,SAPStatus,SAPStatusText,ImageUrl," +
                                "QualityLossBig,QualityLossSmall,VersionNo,OccurrenceUnitID,ResponsibleUnitID," +
                                "StationLeaderID," +
                                "IsSituationTrue,LossMoney,BackHour,IsOverQuato,IsSignificantQualityLoss,IsNCR," +
                                "NcrTaskNo,NcrTaskID,CauseReason) VALUES" +
                                "(:Code,:FlowType,:FlowID," +
                                ":UpFlowID," +
                                ":FollowerID,:Status,:StatusText,:CreateTime,:SubmitTime,:OrderID,:PartID," +
                                ":PartPointID,:Disposal,:CraftsmanIDs,:TechnicalEngineerIDs,:SAPStatus," +
                                ":SAPStatusText,:ImageUrl,:QualityLossBig,:QualityLossSmall,:VersionNo," +
                                ":OccurrenceUnitID," +
                                ":ResponsibleUnitID,:StationLeaderID,:IsSituationTrue,:LossMoney,:BackHour," +
                                ":IsOverQuato,:IsSignificantQualityLoss,:IsNCR,:NcrTaskNo,:NcrTaskID,:CauseReason);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_qualitylossbpm SET Code = :Code,FlowType = :FlowType," +
                                "FlowID = :FlowID,UpFlowID = :UpFlowID,FollowerID = :FollowerID,Status = :Status," +
                                "StatusText = :StatusText,CreateTime = :CreateTime,SubmitTime = now(),OrderID =" +
                                " " +
                                ":OrderID,PartID = :PartID,PartPointID = :PartPointID,Disposal = :Disposal," +
                                "CraftsmanIDs = :CraftsmanIDs,TechnicalEngineerIDs = :TechnicalEngineerIDs,SAPStatus " +
                                "= :SAPStatus," +
                                "SAPStatusText = :SAPStatusText,ImageUrl = :ImageUrl," +
                                "QualityLossBig = :QualityLossBig,QualityLossSmall = :QualityLossSmall,VersionNo = " +
                                ":VersionNo,OccurrenceUnitID=:OccurrenceUnitID,ResponsibleUnitID=:ResponsibleUnitID," +
                                "StationLeaderID=:StationLeaderID,IsSituationTrue=:IsSituationTrue," +
                                "LossMoney=:LossMoney," +
                                "BackHour=:BackHour,IsOverQuato=:IsOverQuato," +
                                "IsSignificantQualityLoss=:IsSignificantQualityLoss,IsNCR=:IsNCR," +
                                "NcrTaskNo=:NcrTaskNo,NcrTaskID=:NcrTaskID,CauseReason=:CauseReason WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCQualityLossBPM.ID);
            wParamMap.put("Code", wSFCQualityLossBPM.Code);
            wParamMap.put("FlowType", wSFCQualityLossBPM.FlowType);
            wParamMap.put("FlowID", wSFCQualityLossBPM.FlowID);
            wParamMap.put("UpFlowID", wSFCQualityLossBPM.UpFlowID);
            wParamMap.put("FollowerID", StringUtils.Join(",", wSFCQualityLossBPM.FollowerID));
            wParamMap.put("Status", wSFCQualityLossBPM.Status);
            wParamMap.put("StatusText", wSFCQualityLossBPM.StatusText);
            wParamMap.put("CreateTime", wSFCQualityLossBPM.CreateTime);
            wParamMap.put("SubmitTime", wSFCQualityLossBPM.SubmitTime);
            wParamMap.put("OrderID", wSFCQualityLossBPM.OrderID);
            wParamMap.put("PartID", wSFCQualityLossBPM.PartID);
            wParamMap.put("PartPointID", wSFCQualityLossBPM.PartPointID);
            wParamMap.put("Disposal", wSFCQualityLossBPM.Disposal);
            wParamMap.put("CraftsmanIDs", wSFCQualityLossBPM.CraftsmanIDs);
            wParamMap.put("TechnicalEngineerIDs", wSFCQualityLossBPM.TechnicalEngineerIDs);
            wParamMap.put("SAPStatus", wSFCQualityLossBPM.SAPStatus);
            wParamMap.put("SAPStatusText", wSFCQualityLossBPM.SAPStatusText);
            wParamMap.put("ImageUrl", wSFCQualityLossBPM.ImageUrl);
            wParamMap.put("QualityLossBig", wSFCQualityLossBPM.QualityLossBig);
            wParamMap.put("QualityLossSmall", wSFCQualityLossBPM.QualityLossSmall);
            wParamMap.put("VersionNo", wSFCQualityLossBPM.VersionNo);
            wParamMap.put("OccurrenceUnitID", wSFCQualityLossBPM.OccurrenceUnitID);
            wParamMap.put("ResponsibleUnitID", wSFCQualityLossBPM.ResponsibleUnitID);
            wParamMap.put("StationLeaderID", wSFCQualityLossBPM.StationLeaderID);
            wParamMap.put("IsSituationTrue", wSFCQualityLossBPM.IsSituationTrue);
            wParamMap.put("LossMoney", wSFCQualityLossBPM.LossMoney);
            wParamMap.put("BackHour", wSFCQualityLossBPM.BackHour);
            wParamMap.put("IsOverQuato", wSFCQualityLossBPM.IsOverQuato);
            wParamMap.put("IsSignificantQualityLoss", wSFCQualityLossBPM.IsSignificantQualityLoss);
            wParamMap.put("IsNCR", wSFCQualityLossBPM.IsNCR);
            wParamMap.put("NcrTaskNo", wSFCQualityLossBPM.NcrTaskNo);
            wParamMap.put("NcrTaskID", wSFCQualityLossBPM.NcrTaskID);
            wParamMap.put("CauseReason", wSFCQualityLossBPM.CauseReason);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCQualityLossBPM.getID() <= 0) {
                wSFCQualityLossBPM.setID(Objects.requireNonNull(keyHolder.getKey()).intValue());
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wSFCQualityLossBPM;
    }

    /**
     * 添加或修改（不修改质量损失大类和小类）
     */
    public SFCQualityLossBPM UpdateWithoutQualityLossType(BMSEmployee wLoginUser, SFCQualityLossBPM wSFCQualityLossBPM,
                                                          OutResult<Integer> wErrorCode) {
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wSFCQualityLossBPM;
            }

            if (wSFCQualityLossBPM == null)
                return null;

            String wSQL = "";
            if (wSFCQualityLossBPM.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_qualitylossbpm(Code,FlowType,FlowID,UpFlowID," +
                                "FollowerID,Status,StatusText,CreateTime,SubmitTime,OrderID,PartID,PartPointID," +
                                "Disposal,CraftsmanIDs,TechnicalEngineerIDs,SAPStatus,SAPStatusText,ImageUrl," +
                                "QualityLossBig,QualityLossSmall,VersionNo,OccurrenceUnitID,ResponsibleUnitID," +
                                "StationLeaderID," +
                                "IsSituationTrue,LossMoney,BackHour,IsOverQuato,IsSignificantQualityLoss,IsNCR," +
                                "NcrTaskNo,NcrTaskID,CauseReason) VALUES" +
                                "(:Code,:FlowType,:FlowID," +
                                ":UpFlowID," +
                                ":FollowerID,:Status,:StatusText,:CreateTime,:SubmitTime,:OrderID,:PartID," +
                                ":PartPointID,:Disposal,:CraftsmanIDs,:TechnicalEngineerIDs,:SAPStatus," +
                                ":SAPStatusText,:ImageUrl,:QualityLossBig,:QualityLossSmall,:VersionNo," +
                                ":OccurrenceUnitID," +
                                ":ResponsibleUnitID,:StationLeaderID,:IsSituationTrue,:LossMoney,:BackHour," +
                                ":IsOverQuato,:IsSignificantQualityLoss,:IsNCR,:NcrTaskNo,:NcrTaskID,:CauseReason);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_qualitylossbpm SET Code = :Code,FlowType = :FlowType," +
                                "FlowID = :FlowID,UpFlowID = :UpFlowID,FollowerID = :FollowerID,Status = :Status," +
                                "StatusText = :StatusText,CreateTime = :CreateTime,SubmitTime = now(),OrderID =" +
                                " " +
                                ":OrderID,PartID = :PartID,PartPointID = :PartPointID,Disposal = :Disposal," +
                                "CraftsmanIDs = :CraftsmanIDs,TechnicalEngineerIDs = :TechnicalEngineerIDs,SAPStatus " +
                                "= :SAPStatus," +
                                "SAPStatusText = :SAPStatusText,ImageUrl = :ImageUrl,VersionNo = " +
                                ":VersionNo,OccurrenceUnitID=:OccurrenceUnitID,ResponsibleUnitID=:ResponsibleUnitID," +
                                "StationLeaderID=:StationLeaderID,IsSituationTrue=:IsSituationTrue," +
                                "LossMoney=:LossMoney," +
                                "BackHour=:BackHour,IsOverQuato=:IsOverQuato," +
                                "IsSignificantQualityLoss=:IsSignificantQualityLoss,IsNCR=:IsNCR," +
                                "NcrTaskNo=:NcrTaskNo,NcrTaskID=:NcrTaskID,CauseReason=:CauseReason WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCQualityLossBPM.ID);
            wParamMap.put("Code", wSFCQualityLossBPM.Code);
            wParamMap.put("FlowType", wSFCQualityLossBPM.FlowType);
            wParamMap.put("FlowID", wSFCQualityLossBPM.FlowID);
            wParamMap.put("UpFlowID", wSFCQualityLossBPM.UpFlowID);
            wParamMap.put("FollowerID", StringUtils.Join(",", wSFCQualityLossBPM.FollowerID));
            wParamMap.put("Status", wSFCQualityLossBPM.Status);
            wParamMap.put("StatusText", wSFCQualityLossBPM.StatusText);
            wParamMap.put("CreateTime", wSFCQualityLossBPM.CreateTime);
            wParamMap.put("SubmitTime", wSFCQualityLossBPM.SubmitTime);
            wParamMap.put("OrderID", wSFCQualityLossBPM.OrderID);
            wParamMap.put("PartID", wSFCQualityLossBPM.PartID);
            wParamMap.put("PartPointID", wSFCQualityLossBPM.PartPointID);
            wParamMap.put("Disposal", wSFCQualityLossBPM.Disposal);
            wParamMap.put("CraftsmanIDs", wSFCQualityLossBPM.CraftsmanIDs);
            wParamMap.put("TechnicalEngineerIDs", wSFCQualityLossBPM.TechnicalEngineerIDs);
            wParamMap.put("SAPStatus", wSFCQualityLossBPM.SAPStatus);
            wParamMap.put("SAPStatusText", wSFCQualityLossBPM.SAPStatusText);
            wParamMap.put("ImageUrl", wSFCQualityLossBPM.ImageUrl);
            wParamMap.put("QualityLossBig", wSFCQualityLossBPM.QualityLossBig);
            wParamMap.put("QualityLossSmall", wSFCQualityLossBPM.QualityLossSmall);
            wParamMap.put("VersionNo", wSFCQualityLossBPM.VersionNo);
            wParamMap.put("OccurrenceUnitID", wSFCQualityLossBPM.OccurrenceUnitID);
            wParamMap.put("ResponsibleUnitID", wSFCQualityLossBPM.ResponsibleUnitID);
            wParamMap.put("StationLeaderID", wSFCQualityLossBPM.StationLeaderID);
            wParamMap.put("IsSituationTrue", wSFCQualityLossBPM.IsSituationTrue);
            wParamMap.put("LossMoney", wSFCQualityLossBPM.LossMoney);
            wParamMap.put("BackHour", wSFCQualityLossBPM.BackHour);
            wParamMap.put("IsOverQuato", wSFCQualityLossBPM.IsOverQuato);
            wParamMap.put("IsSignificantQualityLoss", wSFCQualityLossBPM.IsSignificantQualityLoss);
            wParamMap.put("IsNCR", wSFCQualityLossBPM.IsNCR);
            wParamMap.put("NcrTaskNo", wSFCQualityLossBPM.NcrTaskNo);
            wParamMap.put("NcrTaskID", wSFCQualityLossBPM.NcrTaskID);
            wParamMap.put("CauseReason", wSFCQualityLossBPM.CauseReason);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCQualityLossBPM.getID() <= 0) {
                wSFCQualityLossBPM.setID(Objects.requireNonNull(keyHolder.getKey()).intValue());
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wSFCQualityLossBPM;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCQualityLossBPM> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCQualityLossBPM wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_qualitylossbpm WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取最新的编码
     */
    public synchronized String GetNewCode(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser, MESDBSource.APS, 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT Code from {0}.sfc_qualitylossbpm where ID in (SELECT Max(ID) FROM {0}.sfc_qualitylossbpm);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            int wNumber = 1;
            for (Map<String, Object> wReader : wQueryResult) {
                String wCode = StringUtils.parseString(wReader.get("Code"));
                // 截取流水号
                String wSerialNumber = wCode.substring(wCode.length() - 4);
                int wSN = StringUtils.parseInt(wSerialNumber);
                // 截取月份
                String wMonthStr = wCode.substring(wCode.length() - 6, wCode.length() - 4);
                int wMonth = StringUtils.parseInt(wMonthStr);
                if (Calendar.getInstance().get(Calendar.MONTH) != wMonth - 1) {
                    wNumber = 1;
                } else {
                    wNumber = wSN + 1;
                }
            }

            wResult = StringUtils.Format("QL{0}{1}{2}", String.valueOf(Calendar.getInstance().get(Calendar.YEAR)),
                    String.format("%02d", Calendar.getInstance().get(Calendar.MONTH) + 1),
                    String.format("%04d", wNumber));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCQualityLossBPM SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCQualityLossBPM wResult = new SFCQualityLossBPM();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCQualityLossBPM> wList = SelectList(wLoginUser, wID, "", -1, -1, -1,
                    -1, -1, null, null, null, null, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCQualityLossBPM> SelectList(BMSEmployee wLoginUser, int wID, String wCode, int wUpFlowID,
                                              int wOrderID, int wPartID, int wPartPointID, int wSAPStatus,
                                              Calendar wStartTime, Calendar wEndTime, List<Integer> wStatusList,
                                              List<Integer> wNotStatusList, List<Integer> wIDList,
                                              OutResult<Integer> wErrorCode) {
        List<SFCQualityLossBPM> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            if (wStatusList == null) {
                wStatusList = new ArrayList<>();
            }
            if (wNotStatusList == null) {
                wNotStatusList = new ArrayList<>();
            }
            if (wIDList == null) {
                wIDList = new ArrayList<>();
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.Name UpFlowName,t2.LineID,t2.PartNo,t2.OrderNo WBSNo,t2.ProductID," +
                            "t2.BureauSectionID CustomerID,t3.Name PartName,t3.Code PartCode,t4.Name PartPointName," +
                            "t5.Name OccurrenceUnitName,t6.Name ResponsibleUnitName,t7.Name StationLeaderName FROM " +
                            "{0}.sfc_qualitylossbpm t "
                            + " left join {0}.mbs_user t1 on t.UpFlowID=t1.ID "
                            + " left join {0}.oms_order t2 on t.OrderID=t2.ID "
                            + " left join {0}.fpc_part t3 on t.PartID=t3.ID "
                            + " left join {0}.fpc_partpoint t4 on t.PartPointID=t4.ID "
                            + " left join {0}.bms_department t5 on t.OccurrenceUnitID=t5.ID "
                            + " left join {0}.bms_department t6 on t.ResponsibleUnitID=t6.ID "
                            + " left join {0}.mbs_user t7 on t.StationLeaderID=t7.ID "
                            + "WHERE  1=1  " +
                            "and ( :wID <= 0 or :wID = t.ID ) " +
                            "and ( :wCode is null or :wCode = '''' or :wCode = t.Code ) "
                            + "and ( :wStatus is null or :wStatus = '''' or t.Status in ({1})) "
                            + "and ( :wNotStatus is null or :wNotStatus = '''' or t.Status not in ({2})) "
                            + "and ( :wIDs is null or :wIDs = '''' or t.ID in ({3})) "
                            + "and ( :wUpFlowID <= 0 or :wUpFlowID = t.UpFlowID ) " +
                            "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <= t" +
                            ".SubmitTime ) " +
                            "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >= t" +
                            ".CreateTime ) " +
                            "and ( :wOrderID <= 0 or :wOrderID = t.OrderID ) " +
                            "and ( :wPartID <= 0 or :wPartID = t.PartID ) " +
                            "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID ) " +
                            "and ( :wSAPStatus <= 0 or :wSAPStatus = t.SAPStatus );",
                    wInstance.Result,
                    wStatusList.size() > 0 ? StringUtils.Join(",", wStatusList) : "0",
                    wNotStatusList.size() > 0 ? StringUtils.Join(",", wNotStatusList) : "0",
                    wIDList.size() > 0 ? StringUtils.Join(",", wIDList) : "0");

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wCode", wCode);
            wParamMap.put("wUpFlowID", wUpFlowID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wSAPStatus", wSAPStatus);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wStatus", StringUtils.Join(",", wStatusList));
            wParamMap.put("wNotStatus", StringUtils.Join(",", wNotStatusList));
            wParamMap.put("wIDs", StringUtils.Join(",", wIDList));

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCQualityLossBPM wItem = new SFCQualityLossBPM();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.Code = StringUtils.parseString(wReader.get("Code"));
                wItem.FlowType = StringUtils.parseInt(wReader.get("FlowType"));
                wItem.FlowID = StringUtils.parseInt(wReader.get("FlowID"));
                wItem.UpFlowID = StringUtils.parseInt(wReader.get("UpFlowID"));
                wItem.UpFlowName = StringUtils.parseString(wReader.get("UpFlowName"));
                wItem.FollowerID = StringUtils.parseIntList(StringUtils.parseString(wReader.get("FollowerID")).split(
                        ","));
                wItem.FollowerName = APSUtils.getInstance().GetNames(wItem.FollowerID);
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.StatusText = StringUtils.parseString(wReader.get("StatusText"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.SubmitTime = StringUtils.parseCalendar(wReader.get("SubmitTime"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.CustomerCode = APSConstans.GetCRMCustomerCode(wItem.CustomerID);
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.Disposal = StringUtils.parseString(wReader.get("Disposal"));
                wItem.CraftsmanIDs = StringUtils.parseString(wReader.get("CraftsmanIDs"));
                wItem.CraftsmanNames = APSUtils.getInstance()
                        .GetNames(StringUtils.parseIntList(wItem.CraftsmanIDs.split(",")));
                wItem.TechnicalEngineerIDs = StringUtils.parseString(wReader.get("TechnicalEngineerIDs"));
                wItem.TechnicalEngineerNames = APSUtils.getInstance()
                        .GetNames(StringUtils.parseIntList(wItem.TechnicalEngineerIDs,
                                ","));
                wItem.SAPStatus = StringUtils.parseInt(wReader.get("SAPStatus"));
                wItem.SAPStatusText = StringUtils.parseString(wReader.get("SAPStatusText"));
                wItem.ImageUrl = StringUtils.parseString(wReader.get("ImageUrl"));
                wItem.QualityLossBig = StringUtils.parseString(wReader.get("QualityLossBig"));
                wItem.QualityLossSmall = StringUtils.parseString(wReader.get("QualityLossSmall"));
                wItem.QualityLossBigName = SFCQualityLossBigType.getEnumType(StringUtils.parseInt(wItem.QualityLossBig))
                        .getLable();
                wItem.QualityLossSmallName = SFCQualityLossSmallType
                        .getEnumType(StringUtils.parseInt(wItem.QualityLossSmall)).getLable();
                wItem.VersionNo = StringUtils.parseString(wReader.get("VersionNo"));
                wItem.OccurrenceUnitID = StringUtils.parseInt(wReader.get("OccurrenceUnitID"));
                wItem.OccurrenceUnitName = StringUtils.parseString(wReader.get("OccurrenceUnitName"));
                wItem.ResponsibleUnitID = StringUtils.parseInt(wReader.get("ResponsibleUnitID"));
                wItem.ResponsibleUnitName = StringUtils.parseString(wReader.get("ResponsibleUnitName"));
                wItem.StationLeaderID = StringUtils.parseInt(wReader.get("StationLeaderID"));
                wItem.StationLeaderName = StringUtils.parseString(wReader.get("StationLeaderName"));
                wItem.IsSituationTrue = StringUtils.parseInt(wReader.get("IsSituationTrue"));
                wItem.IsSituationTrueText = wItem.IsSituationTrue == 1 ? "以上情况属实"
                        : wItem.IsSituationTrue == 2 ? "不属实" : "-";
                wItem.LossMoney = StringUtils.parseDouble(wReader.get("LossMoney"));
                wItem.BackHour = StringUtils.parseDouble(wReader.get("BackHour"));
                wItem.IsOverQuato = StringUtils.parseInt(wReader.get("IsOverQuato"));
                wItem.IsOverQuatoText = wItem.IsOverQuato == 1 ? "是" : "否";
                wItem.IsSignificantQualityLoss = StringUtils.parseInt(wReader.get("IsSignificantQualityLoss"));
                wItem.IsSignificantQualityLossText = wItem.IsSignificantQualityLoss == 1 ? "是" : "否";
                wItem.IsNCR = StringUtils.parseInt(wReader.get("IsNCR"));
                wItem.NcrTaskNo = StringUtils.parseString(wReader.get("NcrTaskNo"));
                wItem.NcrTaskID = StringUtils.parseInt(wReader.get("NcrTaskID"));
                wItem.CauseReason = StringUtils.parseString(wReader.get("CauseReason"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    @Override
    public List<BPMTaskBase> BPM_GetUndoTaskList(BMSEmployee wLoginUser, int wResponsorID,
                                                 OutResult<Integer> wErrorCode) {
        List<SFCQualityLossBPM> wResult = new ArrayList<>();
        try {
            // 获取所有任务消息
            List<BFCMessage> wMessageList = CoreServiceImpl.getInstance()
                    .BFC_GetMessageList(wLoginUser, wLoginUser.getID(), BPMEventModule.SFCQualityLoss.getValue(), -1,
                            BFCMessageType.Task.getValue(), 0, -1, null, null)
                    .List(BFCMessage.class);
            wMessageList.addAll(CoreServiceImpl.getInstance().BFC_GetMessageList(wLoginUser, wLoginUser.getID(),
                            BPMEventModule.SFCQualityLoss.getValue(), -1, BFCMessageType.Task.getValue(), 1, -1, null,
                            null)
                    .List(BFCMessage.class));
            wMessageList.addAll(CoreServiceImpl.getInstance().BFC_GetMessageList(wLoginUser, wLoginUser.getID(),
                            BPMEventModule.SFCQualityLoss.getValue(), -1, BFCMessageType.Task.getValue(), 2, -1, null,
                            null)
                    .List(BFCMessage.class));

            List<Integer> wTaskIDList = wMessageList.stream().map(p -> (int) p.MessageID).distinct()
                    .collect(Collectors.toList());

            // 所有未完成的任务
            Map<Integer, SFCQualityLossBPM> wTaskMap = new HashMap<>();
            if (wTaskIDList.size() > 0) {
                List<SFCQualityLossBPM> wMTCTaskListTemp = this.SelectList(wLoginUser, -1, "", -1, -1,
                        -1, -1, -1, null, null, null, null, wTaskIDList, wErrorCode);

                wTaskMap = wMTCTaskListTemp.stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1));
            }
            SFCQualityLossBPM wTaskTemp;
            for (BFCMessage wBFCMessage : wMessageList) {
                if (!wTaskMap.containsKey((int) wBFCMessage.getMessageID()))
                    continue;

                wTaskTemp = CloneTool.Clone(wTaskMap.get((int) wBFCMessage.getMessageID()), SFCQualityLossBPM.class);
                wTaskTemp.StepID = wBFCMessage.getStepID();
                wResult.add(wTaskTemp);
            }

            wResult.sort(Comparator.comparing(SFCQualityLossBPM::getSubmitTime).reversed());
            // 剔除任务状态为0的任务（废弃任务）
            if (wResult.size() > 0) {
                wResult = wResult.stream().filter(p -> p.Status != 0).collect(Collectors.toList());
            }
        } catch (Exception e) {
            wErrorCode.set(MESException.Exception.getValue());
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public List<BPMTaskBase> BPM_GetDoneTaskList(BMSEmployee wLoginUser, int wResponsorID, Calendar wStartTime,
                                                 Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<SFCQualityLossBPM> wResult = new ArrayList<>();
        wErrorCode.set(0);
        try {
            List<SFCQualityLossBPM> wTaskList;
            // 获取所有任务消息
            List<BFCMessage> wMessageList = CoreServiceImpl.getInstance()
                    .BFC_GetMessageList(wLoginUser, wLoginUser.getID(), BPMEventModule.SFCQualityLoss.getValue(), -1,
                            BFCMessageType.Task.getValue(), 3, -1, wStartTime, wEndTime)
                    .List(BFCMessage.class);
            wMessageList
                    .addAll(CoreServiceImpl.getInstance()
                            .BFC_GetMessageList(wLoginUser, wLoginUser.getID(),
                                    BPMEventModule.SFCQualityLoss.getValue(),
                                    -1, BFCMessageType.Task.getValue(), 4, -1, wStartTime, wEndTime)
                            .List(BFCMessage.class));

            List<Integer> wTaskIDList = wMessageList.stream().map(p -> (int) p.MessageID).distinct()
                    .collect(Collectors.toList());

            if (wTaskIDList.size() <= 0) {
                return new ArrayList<>(wResult);
            }

            wTaskList = this.SelectList(wLoginUser, -1, "", -1, -1, -1, -1, -1,
                    wStartTime, wEndTime, null, null, wTaskIDList, wErrorCode);

            wTaskList.sort(Comparator.comparing(SFCQualityLossBPM::getSubmitTime).reversed());

            wResult = wTaskList;
            // 剔除任务状态为0的任务（废弃任务）
            if (wResult.size() > 0) {
                wResult = wResult.stream().filter(p -> p.Status != 0).collect(Collectors.toList());
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public List<BPMTaskBase> BPM_GetSendTaskList(BMSEmployee wLoginUser, int wResponsorID, Calendar wStartTime,
                                                 Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<SFCQualityLossBPM> wResult = new ArrayList<>();
        try {
            wResult = this.SelectList(wLoginUser, -1, "", wResponsorID, -1, -1, -1, -1,
                    wStartTime, wEndTime, null, null, null, wErrorCode);
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return new ArrayList<>(wResult);
    }

    @Override
    public BPMTaskBase BPM_UpdateTask(BMSEmployee wLoginUser, BPMTaskBase wTask, OutResult<Integer> wErrorCode) {
        BPMTaskBase wResult = new BPMTaskBase();
        try {
            if (wTask.Status == 2) {
                wResult = this.Update(wLoginUser, (SFCQualityLossBPM) wTask, wErrorCode);
            } else {
                wResult = this.UpdateWithoutQualityLossType(wLoginUser, (SFCQualityLossBPM) wTask, wErrorCode);
            }

            // 保存bom子项
            if (((SFCQualityLossBPM) wTask).SFCQualityLossBPMItemList != null
                    && ((SFCQualityLossBPM) wTask).SFCQualityLossBPMItemList.size() > 0) {
                // 删除减少的数据
                List<SFCQualityLossBPMItem> wItemList = SFCQualityLossBPMItemDAO.getInstance().SelectList(wLoginUser,
                        -1, wResult.ID, -1, -1, "",
                        wErrorCode);
                List<SFCQualityLossBPMItem> wDeleteList = wItemList.stream()
                        .filter(p -> ((SFCQualityLossBPM) wTask).SFCQualityLossBPMItemList.stream()
                                .noneMatch(q -> q.ID == p.ID))
                        .collect(Collectors.toList());
                SFCQualityLossBPMItemDAO.getInstance().DeleteList(wLoginUser, wDeleteList, wErrorCode);
                // 保存数据
                for (SFCQualityLossBPMItem wSFCQualityLossBPMItem :
                        ((SFCQualityLossBPM) wTask).SFCQualityLossBPMItemList) {
                    wSFCQualityLossBPMItem.SFCQualityLossBPMID = wResult.ID;
                    SFCQualityLossBPMItemDAO.getInstance().Update(wLoginUser, wSFCQualityLossBPMItem, wErrorCode);
                }
            }
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return wResult;
    }

    @Override
    public BPMTaskBase BPM_GetTaskInfo(BMSEmployee wLoginUser, int wTaskID, String wCode,
                                       OutResult<Integer> wErrorCode) {
        SFCQualityLossBPM wResult = new SFCQualityLossBPM();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser, MESDBSource.APS, 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCQualityLossBPM> wList = this.SelectList(wLoginUser, wTaskID, wCode, -1, -1, -1,
                    -1, -1, null, null, null, null, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);

            wResult.SFCQualityLossBPMItemList = SFCQualityLossBPMItemDAO.getInstance().SelectList(wLoginUser, -1,
                    wResult.ID, -1, -1, "", wErrorCode);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    public List<List<String>> QueryExportList(BMSEmployee wLoginUser, Calendar wStartTime, Calendar wEndTime,
                                              OutResult<Integer> wErrorCode) {
        List<List<String>> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t1.Code,t3.ProductNo,t4.Name LineName,t5.CustomerName,t2.PartNo," +
                            "t6.Name PartName,t7.Name PartPointName, " +
                            "t1.QualityLossBig,t1.QualityLossSmall,t1.IsSignificantQualityLoss,t8.Name " +
                            "StationLeaderName,t1.Disposal,t9.Name UpFlowName, " +
                            "t1.CreateTime,t1.StatusText,SAPStatusText,t10.MaterialNo,t10.MaterialName,t11.Name " +
                            "UnitText,t.Quota,t.MaterialNumber,t.ReplaceType,t.OutSourceType,t.SapType FROM {0}" +
                            ".`sfc_qualitylossbpmitem` t  " +
                            "LEFT JOIN {0}.`sfc_qualitylossbpm` t1 ON t.sfcqualitylossbpmid=t1.Id   " +
                            "LEFT JOIN {0}.`oms_order` t2 ON t1.OrderID=t2.ID " +
                            "LEFT JOIN {0}.`fpc_product` t3 ON t2.ProductID=t3.ID " +
                            "LEFT JOIN {0}.`fmc_line` t4 ON t2.LineID=t4.ID " +
                            "LEFT JOIN {0}.`crm_customer` t5 ON t2.BureauSectionID=t5.ID " +
                            "LEFT JOIN {0}.`fpc_part` t6 ON t1.PartID=t6.ID " +
                            "LEFT JOIN {0}.`fpc_partpoint` t7 ON t1.PartPointID=t7.ID " +
                            "LEFT JOIN {0}.`mbs_user` t8 ON t1.StationLeaderID=t8.ID " +
                            "LEFT JOIN {0}.`mbs_user` t9 ON t1.UpFlowID=t9.ID " +
                            "LEFT JOIN {0}.`mss_material` t10 ON t.MaterialID=t10.ID " +
                            "LEFT JOIN {0}.`cfg_unit` t11 ON t10.CYUnitID=t11.ID " +
                            " WHERE t1.Status NOT IN (-1,0,22) and :wStartTime < t1.SubmitTime and :wEndTime > t1" +
                            ".CreateTime ORDER BY t1.Code ASC; ",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            int wIndex = 1;
            for (Map<String, Object> wReader : wQueryResult) {

                String wCode = StringUtils.parseString(wReader.get("Code"));
                String wProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                String wLineName = StringUtils.parseString(wReader.get("LineName"));
                String wCustomerName = StringUtils.parseString(wReader.get("CustomerName"));
                String wPartNo = StringUtils.parseString(wReader.get("PartNo"));
                String wPartName = StringUtils.parseString(wReader.get("PartName"));
                String wPartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                int wQualityLossBig = StringUtils.parseInt(wReader.get("QualityLossBig"));
                int wQualityLossSmall = StringUtils.parseInt(wReader.get("QualityLossSmall"));
                int wIsSignificantQualityLoss = StringUtils.parseInt(wReader.get("IsSignificantQualityLoss"));
                String wStationLeaderName = StringUtils.parseString(wReader.get("StationLeaderName"));
                String wDisposal = StringUtils.parseString(wReader.get("Disposal"));
                String wUpFlowName = StringUtils.parseString(wReader.get("UpFlowName"));
                Calendar wCreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                String wStatusText = StringUtils.parseString(wReader.get("StatusText"));
                String wSAPStatusText = StringUtils.parseString(wReader.get("SAPStatusText"));
                String wMaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                String wMaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                String wUnitText = StringUtils.parseString(wReader.get("UnitText"));
                double wQuota = StringUtils.parseDouble(wReader.get("Quota"));
                double wMaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                int wReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                int wOutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                int wSapType = StringUtils.parseInt(wReader.get("SapType"));

                List<String> wValueList = new ArrayList<>(Arrays.asList(String.valueOf(wIndex), wCode, wProductNo,
                        wLineName, wCustomerName, wPartNo, wPartName, wPartPointName,
                        SFCQualityLossBigType.getEnumType(wQualityLossBig).getLable(),
                        SFCQualityLossSmallType.getEnumType(wQualityLossSmall).getLable(),
                        wIsSignificantQualityLoss == 1 ? "是" : "否", wStationLeaderName, wDisposal, wUpFlowName,
                        wSDF.format(wCreateTime.getTime()), wStatusText, wSAPStatusText, wMaterialNo, wMaterialName,
                        wUnitText, String.valueOf(wQuota), String.valueOf(wMaterialNumber),
                        SFCReplaceType.getEnumType(wReplaceType).getLable(),
                        SFCOutSourceType.getEnumType(wOutSourceType).getLable(),
                        SAPType.getEnumType(wSapType).getLable()));

                wResult.add(wValueList);

                wIndex++;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 获取导出数据
     */
    public MyExcelSheet GetMyExcelSheet(BMSEmployee wLoginUser, Calendar wStartTime, Calendar wEndTime,
                                        OutResult<Integer> wErrorCode) {
        MyExcelSheet wMyExcelSheet = new MyExcelSheet();
        try {
            wMyExcelSheet.SheetName = "质量损失列表";
            wMyExcelSheet.TitleName = "质量损失列表";
            wMyExcelSheet.HeaderList = new ArrayList<>(Arrays.asList("序号", "质量损失单号", "车型", "修程", "配属局段", "车号", "工位",
                    "工序", "质量损失大类", "质量损失小类", "重大质量损失", "工位负责人", "描述", "申请人", "申请时间", "状态", "SAP推送状态", "物资编码", "物料名称",
                    "单位", "台车定额", "损失数量", "必换偶换", "必修偶修", "评估类型"));
            wMyExcelSheet.DataList = QueryExportList(wLoginUser, wStartTime, wEndTime, wErrorCode);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wMyExcelSheet;
    }
}
